﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace DAL {
	public class DBHelper {
		// 连接对象
		private static SqlConnection cnn = null;
		public static SqlConnection Cnn {
			get {
				if (cnn == null) {
					//string cnnstr = "Data Source=CN-20161104SDEM;Initial Catalog=Valuation;Persist Security Info=True;User ID=sa ;Password=123456";
					string cnnstr = "Data Source=47.93.188.10;Initial Catalog=Valuation;Persist Security Info=True;User ID=sa;PassWord=a111111.";
					cnn = new SqlConnection(cnnstr);
					cnn.Open();
				}
				return cnn;
			}
		}
		/// <summary>
		/// 返回最后一次插入数据的id（数据库：sql server） 
		/// </summary>
		/// <param name="sql"></param>
		/// <returns></returns>
		public static int ExecuteScalar(string sql) {
			int i = 0;
			SqlCommand cmd = new SqlCommand(sql, Cnn);
			try {
				if (Cnn.State != ConnectionState.Open) {
					Cnn.Open();
				}
				i = Convert.ToInt32(cmd.ExecuteScalar());
			}
			catch (Exception ex) {
			}
			finally {
				Cnn.Close();
			}
			return i;
		}
		public static DataTable GetDataTable(string sql) {
			//cnn.Open();
			SqlDataAdapter sda = new SqlDataAdapter(sql, Cnn);
			DataTable dt = new DataTable();
			sda.Fill(dt);
			sda.Dispose();
			if (cnn.State == ConnectionState.Open) {
				cnn.Close();
			}
			return dt;
		}
		public static DataTable GetDataTable(string sql, SqlParameter[] spart) {
			SqlCommand comm = new SqlCommand(sql, Cnn);
			comm.CommandType = CommandType.StoredProcedure;
			if (spart != null) {
				comm.Parameters.AddRange(spart);
			}
			SqlDataAdapter sda = new SqlDataAdapter(comm);
			DataTable dt = new DataTable();
			sda.Fill(dt);
			return dt;
		}
		//定义聚集函数查询的方法(重载)
		public static int GetScalar(string sql) {
			Cnn.Close();
			Cnn.Open();
			SqlCommand cmm = new SqlCommand(sql, Cnn);
			object obj = cmm.ExecuteScalar();
			//return (int)cmm.ExecuteScalar(); 
			//------------------------------------------------------------------
			if (obj != null)
				return int.Parse(cmm.ExecuteScalar().ToString());
			else
				return 0;// -1; //为-1时SaveMySchedule.Page_Load时间出现if (scheduleId !=0)逻辑错误
		}
		public static int GetScalar(string sql, SqlParameter[] sp) {
			SqlCommand cmm = new SqlCommand(sql, Cnn);
			cmm.CommandType = CommandType.StoredProcedure;
			if (sp != null) {
				cmm.Parameters.AddRange(sp);
			}
			return int.Parse(cmm.ExecuteScalar().ToString()); //error:(int)cmm.ExecuteScalar();执行MyNoteService.AddMyNote出现错误
		}
		/// <summary>
		/// 定义操作(增删改)数据库的方法(重载)
		/// </summary>
		/// <param name="sql"></param>
		/// <returns></returns>
		public static int ExecuteCommand(string sql) {
			int i = 0;
			SqlCommand cmm = new SqlCommand();
			try {
				if (Cnn.State != ConnectionState.Open) {
					Cnn.Open();
				}
				cmm.CommandText = sql;
				cmm.Connection = Cnn;//不是cnn
				i = cmm.ExecuteNonQuery();
			}
			catch { }
			finally {
				cmm.Dispose();
				Cnn.Close();
			}
			return i;
		}
		/// <summary>
		/// 增删改
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="sp"></param>
		/// <returns></returns>
		public static int ExecuteCommand(string sql, SqlParameter[] sp) {
			SqlCommand cmm = new SqlCommand();
			cmm.CommandText = sql;
			cmm.Connection = Cnn;//不是cnn+
			cmm.CommandType = CommandType.StoredProcedure;
			//cmm.Parameters.Clear();
			cmm.Parameters.AddRange(sp);
			return cmm.ExecuteNonQuery();
		}
		/// <summary>
		/// 定义得到记录集的查询方法(不含参)
		/// </summary>
		/// <param name="sql"></param>
		/// <returns></returns>
		public static SqlDataReader GetDataReader(string sql) {
			SqlCommand cmm = new SqlCommand(sql, Cnn);
			return cmm.ExecuteReader();
		}
		/// <summary>
		/// 数据读取（含参数）
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="sp"></param>
		/// <returns></returns>
		public static SqlDataReader GetDataReader(string sql, SqlParameter[] sp) {
			SqlCommand cmm = new SqlCommand(sql, Cnn);
			cmm.CommandType = CommandType.StoredProcedure;
			if (sp != null) {
				cmm.Parameters.AddRange(sp);
			}
			return cmm.ExecuteReader();
		}
		public static int ExecuteNonQuery(string sql) {
			Cnn.Close();
			Cnn.Open();
			SqlCommand cmm = new SqlCommand(sql, Cnn);
			return cmm.ExecuteNonQuery();
		}
		public static int ExecuteNonQuery(string sql, SqlParameter[] spart) {
			SqlCommand cmm = new SqlCommand(sql, Cnn);
			cmm.CommandType = CommandType.StoredProcedure;
			if (spart != null) {
				cmm.Parameters.AddRange(spart);
			}
			return cmm.ExecuteNonQuery();
		}
		public static List<T> GetList<T>(string sql) {
			DataTable dt = GetDataTable(sql);
			List<T> items = DataTable2List<T>(dt).ToList();
			return items;
		}
		public static List<T> GetList<T>(string sql, SqlParameter[] spart) {
			DataTable dt = GetDataTable(sql, spart);
			List<T> items = DataTable2List<T>(dt).ToList();
			return items;
		}
		/// <summary>
		/// DataSet装换为泛型集合     
		/// </summary>     
		/// <typeparam name="T"></typeparam>    
		/// <param name="p_DataSet">DataSet</param>    
		/// <param name="p_TableIndex">待转换数据表索引</param>    
		/// <returns></returns>  
		private static IList<T> DataTable2List<T>(DataTable p_Data) {
			// 反射技术
			// 返回值初始化       
			IList<T> result = new List<T>();
			for (int j = 0; j < p_Data.Rows.Count; j++) {
				T _t = (T)Activator.CreateInstance(typeof(T));
				PropertyInfo[] propertys = _t.GetType().GetProperties();
				foreach (PropertyInfo pi in propertys) {
					for (int i = 0; i < p_Data.Columns.Count; i++) {
						// 属性与字段名称一致的进行赋值       
						if (pi.Name.Equals(p_Data.Columns[i].ColumnName)) {
							// 数据库NULL值单独处理       
							if (p_Data.Rows[j][i] != DBNull.Value)
								pi.SetValue(_t, p_Data.Rows[j][i], null);
							else
								pi.SetValue(_t, null, null);
							break;
						}
					}
				}
				result.Add(_t);
			}
			return result;
		}
	}
}